<?php
/**
 * database (mysql) class
 *
 * PHP version 5
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @version   SVN: $Id: mysql.class.php 122 2010-08-20 10:20:59Z sruttloff@googlemail.com $
 * @link      phpmy
 */
require_once dirname(__FILE__) . '/mysql.function.class.php';
/**
 * DB Access to MySql
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class mysql extends mysqlFunction
{
    /**
     * @var link id of connect
     */
    protected $link_id = null;
    /**
     * @var result id from last request
     */
    protected $result_id = null;
    /**
     * @var status of db connection
     */
    protected $status = null;
    /**
     * @var is db open
     */
    protected $open = false;
    /**
     * @var error text if request fails
     */
    public $errortxt;
    /**
     * disconnect from DB
     *
     * @return void
     */
    public function disconnect()
    {
        $this->open = false;
        mysql_close($this->link_id);
    }
    /**
     * Frees memory from last result id
     *
     * @return void
     */
    public function free()
    {
        if (isset ($this->result_id) && $this->result_id)
        {
            mysql_free_result($this->result_id);
            unset ($this->result_id);
        }
    }
    /**
     * Frees memory from result id
     *
     * @param mixed $resid result id (from query)
     *
     * @return void
     */
    public function freeById($resid)
    {
        mysql_free_result($resid);
    }
    /**
     * Send a query to existing db connection or create one (framework)
     *
     * @param string $query_string DB Query
     *
     * @return mixed result id of query or false in error case
     */
    public function query($query_string)
    {
        if (!$this->open)
            $this->connect();
        $this->result_id = mysql_query($query_string, $this->link_id);
        if ($this->DEBUG)
            $this->debug($query_string);
        if (!$this->result_id)
        {
            $this->errortxt = mysql_error();
            $frontend = frontend::getInstance();
            if (!$frontend->NEWERRORHANDLER)
                die("\n" . $query_string . "\n\n" . $this->errortxt);
            trigger_error("\n" . $query_string . "\n\n" . $this->errortxt, E_USER_ERROR);
            return false;
        }
        return $this->result_id;
    }
    /**
     * moves data pointer
     *
     * @param int   $i  target position of pointer
     * @param mixed $id link id
     *
     * @return bool
     */
    public function movePointer($i, $id = 0)
    {
        if ($id)
            return @mysql_data_seek($id, $i);
        else
            return @mysql_data_seek($this->result_id, $i);
    }
    /**
     * returns the quantity of result datasets from selects only!!!
     *
     * @param mixed $id link id
     *
     * @return int number of rows
     */
    public function numRows($id = 0)
    {
        if ($id)
            return @mysql_num_rows($id);
        else
            return @mysql_num_rows($this->result_id);
    }
    /**
     * returns the quantity of result datasets from insert, update or delete
     *
     * @return int number of affected rows
     */
    public function affectedRows()
    {
        return @mysql_affected_rows($this->link_id);
    }
    /**
     * returns the quantity of result datasets (deprecated)
     *
     * @param mixed $resid link id
     *
     * @return int quantity of datas
     */
    public function numRowsById($resid)
    {
        return mysql_num_rows($resid);
    }
    /**
     * fetch data result in a object
     *
     * @param mixed $result_id link id
     *
     * @return object data object
     */
    public function fetch($result_id = "")
    {
        if (!$result_id)
            return mysql_fetch_object($this->result_id);
        else
            return mysql_fetch_object($result_id);
    }
    /**
     * fetch data result as array
     *
     * @param mixed  $id   link id
     * @param string $TYPE default MYSQL_BOTH
     *
     * @return array
     */
    public function fetchArray($id = 0, $TYPE = MYSQL_BOTH)
    {
        if ($id)
            return mysql_fetch_array($id, $TYPE);
        else
            return mysql_fetch_array($this->result_id, $TYPE);
    }
    /**
     * fetch data result in a object
     *
     * @param mixed $resid link id
     *
     * @return mixed
     */
    public function fetchById($resid)
    {
        return mysql_fetch_object($resid, $this->link_id);
    }
    /**
     * returns the id (autoincrement) from last insert operation
     *
     * @return int last inserted auto incr. value
     */
    public function insertId()
    {
        return mysql_insert_id($this->link_id);
    }
    /**
     * returns error text from last operation
     *
     * @return string error text
     */
    public function error()
    {
        return mysql_error();
    }
    /**
     * start a transaction
     *
     * @return bool
     */
    public function transactionStart()
    {
        $query = 'BEGIN';
        return $this->query($query);
    }
    /**
     * commit a transaction
     *
     * @return bool
     */
    public function transactionCommit()
    {
        $query = 'COMMIT';
        return $this->query($query);
    }
    /**
     * rollback a transaction
     *
     * @return bool
     */
    public function transactionRollback()
    {
        $query = 'ROLLBACK';
        return $this->query($query);
    }
    /**
     * delete records from a db table
     *
     * @param string $table   Tablename
     * @param string $where   where
     * @param int    $limit   data limit
     * @param string $intable in case of join, you must decide table where to delete
     *
     * @return int number of affected rows
     */
    public function deleteRows($table = null, $where = null, $limit = null, $intable = "")
    {
        if (is_null($where))
            $where = 1;
        if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table))
            $table = '`' . $table . '`';
        $limitview = (string) null;
        if (!is_null($limit))
            $limitview = ' LIMIT ' . $limit;
        $query = 'DELETE ' . $intable . ' FROM ' . $table . ' WHERE ' . $where . $limitview . ';';
        if (defined('DEBUG_QUERY'))
            trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
        return $this->affectedRows($this->query($query));
    }
    /**
     * Querie a db, return row as object
     *
     * @param string|array $cols colums to select
     * @param string       $tbl  Tablename
     * @param string       $whr  where clause
     * @param string       $oby  order by col. names
     * @param int          $o    data offset
     * @param int          $l    data limit
     * @param bool         $f    return as array (default = false)
     *
     * @return mixed Object with results or array with objects
     */
    public function selectRows($cols = array (), $tbl = null, $whr = null, $oby = null, $o = 0, $l = null, $f = false)
    {
        if (!$whr)
            $whr = 1;
        $limitview = (string) null;
        if (!is_null($l))
            $limitview = ' LIMIT ' . $o . ',' . $l;
        if (!is_array($cols))
            $cols = array($cols);
        // secure column names
        foreach ($cols as $k => $v)
        {
            if (!preg_match('=^`.*`$=s', $v) && $v != '*' && !preg_match('=(\.|\sas\s|null|\(.*\))=si', $v))
                $cols[$k] = '`' . $v . '`';
            else
                $cols[$k] = $v;
        }
        if ($oby)
            $oby = ' ORDER BY ' . $oby;
        if (!preg_match('=^`.*`$=s', $tbl) && !preg_match('=(\.|as|\(.*\))=si', $tbl))
            $tbl = '`' . $tbl . '`';
        $query = 'SELECT ' . implode(',', $cols) . ' FROM ' . $tbl . ' WHERE ' . $whr . $oby . $limitview . ';';
        if (defined('DEBUG_QUERY') && DEBUG_QUERY == 1)
            trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
        if (!$result = $this->query($query))
            return false;
        $i_num = $this->numRows($result);
        if ($i_num == 1 && !$f)
            return $this->fetch($result);
        $object = array();
        while ($row = $this->fetch($result))
        {
            $object[] = $row;
        }
        return $object;
    }
    /**
     * Update datas in a db table
     *
     * @param array  $columns colums and values assoc array (FIELD='value')
     * @param string $table   Tablename
     * @param string $where   where
     * @param int    $limit   number of dataset limit. e.g. 10
     * @param string $orderby dataset order by
     *
     * @return bool true or false result
     */
    public function updateRows($columns = array (), $table = null, $where = null, $limit = null, $orderby = null)
    {
        if (is_null($table) || is_null($where))
            return false;
        if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table))
            $table = '`' . $table . '`';
        $setquery = (string) '';
        // secure column names
        foreach ($columns as $k => $v)
        {
            if ($setquery)
                $setquery .= ', ';
            if (!preg_match('=^`.*`$=s', $k) && !preg_match('=(\.|as|\(.*\))=si', $k))
                $setquery .= '`' . $k . '`=';
            else
                $setquery .= $k . '=';
            // is the value a mysql function
            $setquery .= is_null($v) ? 'NULL' : $this->isMYSQLFunction($v);
        }
        $qlimit = $qorderby = (string) null;
        if (!is_null($limit))
            $qlimit = ' LIMIT ' . $limit;
        if (!is_null($orderby))
            $qorderby = ' ORDER BY ' . $orderby;
        $query = 'UPDATE ' . $table . ' SET ' . $setquery . ' WHERE ' . $where . $qorderby . $qlimit . ';';
        if (defined('DEBUG_QUERY') && DEBUG_QUERY == 1)
            trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
        return $this->query($query);
    }
    /**
     * insert a row in a db table
     *
     * @param array  $columns colums and values assoc array (FIELD='value')
     * @param string $table   Tablename
     *
     * @return bool true or false result
     */
    public function insertRow($columns = array (), $table = null)
    {
        if (is_null($table))
            return false;
        if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table))
            $table = '`' . $table . '`';
        $setquery = (string) '';
        // secure column names
        foreach ($columns as $k => $v)
        {
            if ($setquery)
                $setquery .= ', ';
            if (!preg_match('=^`.*`$=s', $k)
                && !preg_match('=(\.|\sas\s|null|\(.*\))=si', $k)
                && $v != 'NULL' && !is_null($v)
            )
                $setquery .= '`' . $k . '`=';
            else
                $setquery .= $k . '=';
            $setquery .= $this->isMYSQLFunction($v);
        }
        $query = 'INSERT INTO ' . $table . ' SET ' . $setquery . ';';
        if (defined('DEBUG_QUERY') && DEBUG_QUERY == 1)
            trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
        return $this->query($query);
    }
    /**
     * check if a named table exists
     *
     * @param string $name table name
     *
     * @return bool
     */
    public function tableExists ($name)
    {
        if (!$this->open)
            $this->connect();
        $query = 'SHOW tables FROM `' . $this->DBNAME . '` like "' . addslashes($name) . '"';
        $result = $this->query($query);
        return $this->numRows($result);
    }
    /**
     * returns a search query for a value in a field
     *
     * @param array  $columns column names where to look in
     * @param string $text    + and / or - words to look for
     *
     * @return string statement for use in where clause
     */
    public function buildMatchingTextQuery($columns = array (), $text = null)
    {
        $searchquery = null;
        $searchstring = array ();
        $pattern = '=([\+\-]?".+")=siU';
        $text = trim(stripslashes($text));
        while (preg_match($pattern, $text, $searchstring2))
        {
            $text = preg_replace('=' . preg_quote($searchstring2[1]) . '=siU', '', $text);
            unset ($searchstring2[0]);
            $searchstring = array_merge($searchstring, $searchstring2);
        }
        if (!$searchwords = explode(' ', trim($text)))
            $searchwords = $text ? array (trim($text)) : null;
        // combine
        if (is_array($searchstring))
        {
            $searchstring = str_replace('"', '', $searchstring);
            if (is_array($searchwords))
                $searchwords = array_merge($searchwords, $searchstring);
            else
                $searchwords = $searchstring;
        }
        if (is_array($columns))
            $searchrows = $columns;
        else
            $searchrows = array ($columns);
        // Analyse
        $first = false;
        // concat searchrows
        $concat = '(' . implode(',', $columns) . ')';
        $searchquery .= " (";
        $first = false;
        for ($x = 0; $x < count($searchwords); $x++)
        {
            if (!$searchwords[$x])
                continue;
            if (!$first)
            {
                $first = true;
                //erstes Wort?
                switch (substr($searchwords[$x], 0, 1))
                {
                    case "-" :
                        $searchquery .=  "'" . substr($searchwords[$x], 0, strlen($searchwords[$x]))
                            . "' NOT IN " . $concat;
                        break;
                    case '+' :
                        $searchquery .= "'" . substr($searchwords[$x], 0, strlen($searchwords[$x]))
                            . "' IN " . $concat;
                        break;
                    default :
                        $searchquery .= "'" . $searchwords[$x] ."' IN " . $concat . ' ';
                        break;
                }
            }
            else
            {
                switch (substr($searchwords[$x], 0, 1))
                {
                    case "-" :
                        $searchquery .= "AND '"
                            . substr($searchwords[$x], 1, strlen($searchwords[$x]))
                            . "' NOT IN " . $concat ;
                        break;
                    case "+" :
                        $searchquery .= "AND '"
                            . substr($searchwords[$x], 1, strlen($searchwords[$x]))
                            . "' IN " . $concat;
                        break;
                    default :
                        $searchquery .= "OR '" . $searchwords[$x] . "' IN " . $concat;
                        break;
                }
            }
        }
        $searchquery .= ") ";
        return $searchquery == " () " ? " 1 " : $searchquery;
    }
    /**
     * returns a fulltext search with like statement
     *
     * @param array  $columns column names where to look in
     * @param string $text    + and / or - words to look for
     *
     * @return string statement for use in where clause
     */
    public function buildFullTextQuery($columns = array (), $text = null)
    {
        $searchquery = null;
        $searchstring = array ();
        $pattern = '=([\+\-]?".+")=siU';
        $text = trim(stripslashes($text));
        while (preg_match($pattern, $text, $searchstring2))
        {
            $text = preg_replace('=' . preg_quote($searchstring2[1]) . '=siU', '', $text);
            unset ($searchstring2[0]);
            $searchstring = array_merge($searchstring, $searchstring2);
        }
        if (!$searchwords = explode(' ', trim($text)))
            $searchwords = $text ? array (trim($text)) : null;
        // combine
        if (is_array($searchstring))
        {
            $searchstring = str_replace('"', '', $searchstring);
            if (is_array($searchwords))
                $searchwords = array_merge($searchwords, $searchstring);
            else
                $searchwords = $searchstring;
        }
        if (is_array($columns))
            $searchrows = $columns;
        else
            $searchrows = array ($columns);
        // Analyse
        $first = false;
        // take care to be converted as string
        foreach ($columns as $k=>$v)
        {
            $columns[$k] = "CAST(".$v." AS CHAR)";
        }
        // concat searchrows
        $concat = "CONCAT_WS(' '," . implode(",", $columns) . ")";
        $searchquery .= " (";
        $first = false;
        for ($x = 0; $x < count($searchwords); $x++)
        {
            if (!$searchwords[$x])
                continue;
            if (!$first)
            {
                $first = true;
                //erstes Wort?
                switch (substr($searchwords[$x], 0, 1))
                {
                    case "-" :
                        $searchquery .= $concat . " NOT Like '%"
                            . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                        break;
                    case "+" :
                        $searchquery .= $concat . " Like '%"
                            . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                        break;
                    default :
                        $searchquery .= $concat . " Like '%$searchwords[$x]%' ";
                        break;
                }
            }
            else
            {
                switch (substr($searchwords[$x], 0, 1))
                {
                    case "-" :
                        $searchquery .= "AND " . $concat ." NOT Like '%"
                            . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                        break;
                    case "+" :
                        $searchquery .= "AND " . $concat . " Like '%"
                            . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                        break;
                    default :
                        $searchquery .= "OR " . $concat . " Like '%$searchwords[$x]%' ";
                        break;
                }
            }
        }
        $searchquery .= ") ";
        return $searchquery == " () " ? " 1 " : $searchquery;
    }
    /**
     * debug output
     *
     * @param string $txt debug msg
     *
     * @return void
     */
    protected function debug($txt)
    {
        echo $txt . '<br />' . "\r\n";
    }
}